﻿using System.Collections.Generic;
using System.Linq;
using DataScripter.Interfaces;

namespace DataScripter.Database.SqlServer
{
    class SqlServerTableInfoProvider : ITableInfoProvider
    {
        private readonly Db _database;
        private string SchemaName { get; set; }
        private string TableName { get; set; }

        private const string FIND_COLUMN_NAMES = @"
SELECT  [COLUMNS].[COLUMN_NAME]
       ,[COLUMNS].[DATA_TYPE]
       ,[COLUMNS].[CHARACTER_MAXIMUM_LENGTH]
       ,[COLUMNS].[NUMERIC_PRECISION]
       ,[COLUMNS].[NUMERIC_SCALE]
       ,[sc].[is_identity]
FROM    [INFORMATION_SCHEMA].COLUMNS
        INNER JOIN sys.columns sc ON [sc].[name] = [COLUMNS].[COLUMN_NAME]
        INNER JOIN sys.tables ON sc.object_id = sys.tables.object_id
                                 AND sys.tables.name = TABLE_NAME
WHERE [COLUMNS].[TABLE_NAME] = '{0}'
AND [COLUMNS].[TABLE_SCHEMA] = '{1}'";

        private const string FIND_SINGLE_COLUMN_NAME = FIND_COLUMN_NAMES + @"
        AND [COLUMNS].[COLUMN_NAME]='{2}'";

        private const string FIND_PRIMARY_KEY_COLUMN_NAMES = @"
SELECT  ccu.[COLUMN_NAME]
FROM    [INFORMATION_SCHEMA].TABLE_CONSTRAINTS tc
        INNER JOIN [INFORMATION_SCHEMA].CONSTRAINT_COLUMN_USAGE ccu ON tc.[CONSTRAINT_NAME] = ccu.[CONSTRAINT_NAME]
WHERE   tc.[CONSTRAINT_TYPE] = 'PRIMARY KEY'
        AND tc.[TABLE_NAME] = '{0}'
        AND [tc].[TABLE_SCHEMA] = '{1}'";

        private const string FIND_FOREIGN_KEYS = @"
SELECT  [sys].[foreign_keys].[name] FOREIGN_KEY_NAME
       ,SCHEMA_NAME([sys].[foreign_keys].[schema_id]) SCHEMA_NAME
       ,OBJECT_NAME([sys].[foreign_keys].[parent_object_id]) TABLE_NAME
       ,[parent_columns].[name] COLUMN_NAME
       ,SCHEMA_NAME([sys].[tables].[schema_id]) REFERENCED_SCHEMA_NAME
       ,OBJECT_NAME([sys].[foreign_keys].[referenced_object_id]) REFERENCED_TABLE_NAME
       ,[referenced_columns].[name] REFERENCED_COLUMN_NAME
FROM    sys.[foreign_keys]
        INNER JOIN sys.[tables] ON [sys].[foreign_keys].[referenced_object_id] = [sys].[tables].[object_id]
        INNER JOIN sys.[foreign_key_columns] ON [sys].[foreign_keys].[object_id] = [sys].[foreign_key_columns].[constraint_object_id]
                                                AND [sys].[foreign_key_columns].[constraint_column_id] = 1
        INNER JOIN sys.[columns] parent_columns ON [parent_columns].[object_id] = [sys].[foreign_keys].[parent_object_id]
                                                   AND [parent_columns].[column_id] = [sys].[foreign_key_columns].[parent_column_id]
        INNER JOIN sys.[columns] referenced_columns ON [referenced_columns].[object_id] = [sys].[foreign_keys].[referenced_object_id]
                                                       AND [referenced_columns].[column_id] = [sys].[foreign_key_columns].[referenced_column_id]
WHERE   [sys].[foreign_keys].[parent_object_id] = OBJECT_ID('[{1}].[{0}]')";

        private const string FIND_FOREIGN_KEYS_POINTING_TO_THIS_TABLE =
            @"
SELECT  [sys].[foreign_keys].[name] FOREIGN_KEY_NAME
       ,SCHEMA_NAME([sys].[foreign_keys].[schema_id]) SCHEMA_NAME
       ,OBJECT_NAME([sys].[foreign_keys].[parent_object_id]) TABLE_NAME
       ,[parent_columns].[name] COLUMN_NAME
       ,SCHEMA_NAME([sys].[tables].[schema_id]) REFERENCED_SCHEMA_NAME
       ,OBJECT_NAME([sys].[foreign_keys].[referenced_object_id]) REFERENCED_TABLE_NAME
       ,[referenced_columns].[name] REFERENCED_COLUMN_NAME
FROM    sys.[foreign_keys]
        INNER JOIN sys.[tables] ON [sys].[foreign_keys].[referenced_object_id] = [sys].[tables].[object_id]
        INNER JOIN sys.[foreign_key_columns] ON [sys].[foreign_keys].[object_id] = [sys].[foreign_key_columns].[constraint_object_id]
                                                AND [sys].[foreign_key_columns].[constraint_column_id] = 1
        INNER JOIN sys.[columns] parent_columns ON [parent_columns].[object_id] = [sys].[foreign_keys].[parent_object_id]
                                                   AND [parent_columns].[column_id] = [sys].[foreign_key_columns].[parent_column_id]
        INNER JOIN sys.[columns] referenced_columns ON [referenced_columns].[object_id] = [sys].[foreign_keys].[referenced_object_id]
                                                       AND [referenced_columns].[column_id] = [sys].[foreign_key_columns].[referenced_column_id]
WHERE   [sys].[foreign_keys].[referenced_object_id] = OBJECT_ID('[{1}].[{0}]')";

        public SqlServerTableInfoProvider(Db database, string schemaName, string tableName)
        {
            _database = database;
            SchemaName = schemaName;
            TableName = tableName;
        }

        public IEnumerable<FieldInfo> GetFields()
        {
            return _database.Execute<FieldInfo>(string.Format(FIND_COLUMN_NAMES, TableName, SchemaName)).ToList();
        }

        public List<FieldInfo> GetPrimaryKeyFields()
        {
            List<string> fieldNames = _database.ExecuteEnumerable<string>(string.Format(FIND_PRIMARY_KEY_COLUMN_NAMES, TableName, SchemaName)).ToList();
            return GetFields().Where(f => fieldNames.Any(pkfn => pkfn == f.ColumnName)).ToList();
        }

        public List<ForeignKeyInfo> GetForeignKeys()
        {
            return _database.Execute<ForeignKeyInfo>(string.Format(FIND_FOREIGN_KEYS, TableName, SchemaName)).ToList();
        }

        public List<ForeignKeyInfo> GetForeignKeysPointingToThisTable()
        {
            return _database.Execute<ForeignKeyInfo>(string.Format(FIND_FOREIGN_KEYS_POINTING_TO_THIS_TABLE, TableName, SchemaName)).ToList();
        }

        public FieldInfo GetField(string lookupColumn)
        {
            return _database.Execute<FieldInfo>(string.Format(FIND_SINGLE_COLUMN_NAME, TableName, SchemaName, lookupColumn)).First();
        }
    }
}
